package com.ldr;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import java.io.*;
import java.nio.charset.StandardCharsets;

/**
 * @author：ldr
 * @date：2025/1/14 19:21
 * @description：将Excel表格转换为strings.xml文件
 */
public class ExcelToStringsXml {
    public static void convertExcelToStringsXmlSingle(String excelFilePath, String outputFileName) throws IOException {
        FileInputStream fileInputStream = new FileInputStream(excelFilePath);
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);

        // 准备输出文件
        File outputFile = new File(outputFileName);

        try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outputFile), StandardCharsets.UTF_8))) {
            writer.write("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n");
            writer.write("<resources>\n");

            // 遍历数据行
            for (int i = 1; i <= sheet.getLastRowNum(); i++) { // 从第 1 行开始（跳过标题行）
                Row row = sheet.getRow(i);
                if (row == null) continue;

                Cell nameCell = row.getCell(0); // 第 1 列
                Cell valueCell = row.getCell(1); // 第 2 列

                if (nameCell == null || valueCell == null) continue;

                String name = getCellStringValue(nameCell);
                String value = getCellStringValue(valueCell);

                // 转义特殊字符
                value = escapeXml(value);

                writer.write(String.format("    <string name=\"%s\">%s</string>\n", name, value));
            }

            writer.write("</resources>");
        }

        workbook.close();
        fileInputStream.close();
    }

    private static String getCellStringValue(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula(); // 或者尝试计算公式值
            case BLANK:
                return "";
            default:
                return "";
        }
    }

    private static String escapeXml(String value) {
        if (value == null) return "";
        return value.replace("&", "&amp;")
                .replace("<", "&lt;")
                .replace(">", "&gt;")
                .replace("\"", "&quot;")
                .replace("'", "&#39;");
    }

}
